﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public void MapQVW_MCOST(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveQVW_MCOST(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            Result = QueryQVW_MCOST(dwData);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }

            MapQVW_MCOST(ref dwLocal, dwData);

            if (!flgExist)
            {
                InsertQVW_MCOST(dwData, 1);
            }
            UpdateQVW_MCOST(dwLocal);

        }
        public void InsertQVW_MCOST(DataRow dwData, int option = 1)
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1://20140501
                    or1.CommandText = "INSERT INTO QVW_MCOST  "
                        + " SELECT  GABLO AS LO,GABLB AS LB,GABBUCHDAT AS WDATE,SUBSTR(GABBUCHDAT,1,4) AS WYEAR,SUBSTR(GABBUCHDAT,5,2) AS WMONTH,"
                        + "SUBSTR(GABBUCHDAT,7,2) AS WDAY,GABAUFTYP AS AUFTYP,GABAUFTYP||GABAUFKRS||GABAUFLFD AS KAUF,GABAUFTYP||GABAUFKRS||GABAUFLFD||GABAUFPOS AS PAUF, GABAUFKRS AS PKRS,GABAUFLFD  AS PLFD,GABAUFPOS AS PPOS,GABAFOLGE AS PFSTEP,"
                        + "GABFC AS BWFC,'G' AS ARTST,GABART AS PART,GABVAR AS PVAR,GABFARBE AS PCOLOR,'            ' AS PDESS,'            ' AS PDCWY,'            ' AS PFINISH,'       ' AS PSIZE,GABBS  AS BS,SUM(GABKG)*0.001 AS UQTY,ROUND(SUM(GABKG)*0.001*MAX(STDCOST),2) AS STDCOST,ROUND(SUM(GABKG)*0.001*MAX(EKCOST),2) AS EKCOST,"
                        + "ROUND(SUM(GABKG)*0.001*MAX(PLANCOST),2) AS PLANCOST"
                        + " FROM GABEW W LEFT JOIN QVW_GSDAT S ON   GABART=S.PART AND GABVAR=S.PVAR "
                        + " WHERE GABMANDANT=:MANDANT AND GABBUCHDAT>'20140701' AND GABBS IN ('01','10','11','12','13','20','21','22','23','26','27','30','31','32','33','34','35','36','37','38','40','41','42','43','46','47','50','51','52','53','55','56','57','58','73','76','77','78','80','88','89')"
                        + " GROUP BY GABMANDANT,GABLO,GABLB,GABBUCHDAT,GABBS,GABFC,GABAUFTYP,GABAUFKRS,GABAUFLFD,GABAUFPOS,GABAFOLGE,GABART,GABVAR,GABFARBE";
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    break;
                case 2:
                    or1.CommandText = "INSERT INTO QVW_MCOST"
                        + " SELECT  WBEWLO AS LO,WBEWLB AS LB,WBEWBUCHDAT AS WDATE,SUBSTR(WBEWBUCHDAT,1,4) AS WYEAR,SUBSTR(WBEWBUCHDAT,5,2) AS WMONTH,SUBSTR(WBEWBUCHDAT,7,2) AS WDAY,WBEWAUFTYP AS AUFTYP,WBEWAUFTYP||WBEWAUFKRS||WBEWAUFLFD AS KAUF,"
                        + "WBEWAUFTYP||WBEWAUFKRS||WBEWAUFLFD||WBEWAUFPOS AS PAUF,WBEWAUFKRS AS PKRS,WBEWAUFLFD AS PLFD,WBEWAUFPOS AS PPOS,WBEWAFOLGE AS PFSTEP,WBEWFC AS BWFC,WBEWWAKZ AS ARTST,WBEWART AS PART,WBEWVAR AS PVAR,WBEWFARBE AS PCOLOR,WBEWDESS AS PDESS,WBEWCOLORIT AS PDCWY,WBEWBESCHICH AS PFINISH,'       ' AS PSIZE,WBEWBS AS BS,SUM(WBEWMTR)*0.001 AS UQTY,ROUND(SUM(WBEWMTR)*0.001*MAX(STDCOST),2) AS STDCOST,ROUND(SUM(WBEWMTR)*0.001*MAX(EKCOST),2) AS EKCOST,ROUND(SUM(WBEWMTR)*0.001*MAX(PLANCOST),2) AS PLANCOST"
                        + " FROM WBBEW W LEFT JOIN QVW_GWASTAMM S ON   WBEWART=S.PART AND WBEWVAR=S.PVAR"
                        + " WHERE WBEWMANDANT=:MANDANT AND WBEWBUCHDAT>'20140701' AND WBEWBS IN ('01','10','11','12','13','20','21','22','23','26','27','30','31','32','33','34','35','36','37','38','40','41','42','43','46','47','50','51','52','53','55','56','57','58','73','76','77','78','80','88','89')"
                        + " GROUP BY WBEWMANDANT,WBEWLO,WBEWLB,WBEWFC,WBEWBUCHDAT,WBEWAUFTYP,WBEWBS,WBEWFC,WBEWAUFKRS,WBEWAUFLFD,WBEWAUFPOS,WBEWAFOLGE,WBEWWAKZ,WBEWART,WBEWVAR,WBEWFARBE,WBEWBESCHICH,WBEWDESS,WBEWCOLORIT";
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    break;
                case 3:
                    or1.CommandText = "INSERT INTO QVW_MCOST"
                        + " SELECT  MBWLO AS LO,MBWLB AS LB,MBWBUCHDAT AS WDATE,SUBSTR(MBWBUCHDAT,1,4) AS WYEAR,SUBSTR(MBWBUCHDAT,5,2) AS WMONTH,SUBSTR(MBWBUCHDAT,7,2) AS WDAY,MBWAUFTYP AS AUFTYP,"
                        + "MBWAUFTYP||MBWAUFKRS||MBWAUFLFD AS KAUF,MBWAUFTYP||MBWAUFKRS||MBWAUFLFD||MBWAUFPOS AS PAUF,MBWAUFKRS AS PKRS,MBWAUFLFD AS PLFD,MBWAUFPOS AS PPOS,MBWAFOLGE AS PFSTEP,MBWFC AS BWFC,'M' AS ARTST,MBWMAT AS PART,MBWVAR AS PVAR,'          '  AS PCOLOR,'          ' AS PDESS,'          ' AS PDCWY,'          ' AS PFINISH,'       ' AS PSIZE,MBWBS AS BS,SUM(MBWME)*0.001 AS UQTY,ROUND(SUM(MBWME)*0.001*MAX(STDCOST),2) AS STDCOST,"
                        + "ROUND(SUM(MBWME)*0.001*MAX(EKCOST),2) AS EKCOST,ROUND(SUM(MBWME)*0.001*MAX(PLANCOST),2) AS PLANCOST"
                        + "  FROM MBEW W LEFT JOIN QVW_MSSTAMM S ON  MBWMAT=S.PART AND MBWVAR=S.PVAR"
                        + " WHERE MBWMANDANT=:MANDANT AND MBWBUCHDAT>'20140701' AND MBWBS IN ('01','10','11','12','13','20','21','22','23','26','27','30','31','32','33','34','35','36','37','38','40','41','42','43','46','47','50','51','52','53','55','56','57','58','73','76','77','78','80','88','89')"
                        + " GROUP BY MBWMANDANT,MBWLO,MBWLB,MBWFC,MBWBUCHDAT,MBWAUFTYP,MBWBS,MBWAUFKRS,MBWAUFLFD,MBWAUFPOS,MBWAFOLGE,MBWMAT,MBWVAR";
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    break;
                case 4:
                    or1.CommandText = "INSERT INTO QVW_MCOST"
                        + " SELECT  FBWLO AS LO,FBWLB AS LB,FBWBUCHDAT AS WDATE,SUBSTR(FBWBUCHDAT,1,4) AS WYEAR,SUBSTR(FBWBUCHDAT,5,2) AS WMONTH,"
                        + "SUBSTR(FBWBUCHDAT,7,2) AS WDAY,FBWAUFTYP AS AUFTYP,FBWAUFTYP||FBWAUFKRS||FBWAUFLFD AS KAUF,FBWAUFTYP||FBWAUFKRS||FBWAUFLFD||FBWAUFPOS AS PAUF,FBWAUFKRS  AS PKRS, FBWAUFLFD  AS PLFD,FBWAUFPOS AS PPOS,"
                        + "FBWAFOLGE AS PFSTEP,FBWFC AS BWFC,'T' AS ARTST,FBWART AS PART,FBWVAR AS PVAR,FBWFARBE AS PCOLOR,'          ' AS PDESS,'          ' AS PDCWY,'          ' AS PFINISH, FBWGROE AS PSIZE,FBWBS AS BS,SUM(FBWME)  AS UQTY,"
                        + "ROUND(SUM(FBWME) *MAX(STDCOST),2) AS STDCOST,ROUND(SUM(FBWME) *MAX(EKCOST),2) AS EKCOST,ROUND(SUM(FBWME) *MAX(PLANCOST),2) AS PLANCOST"
                        + " FROM FBEW W LEFT JOIN QVW_FSSTAMM S ON  FBWART=PART AND FBWVAR=S.PVAR "
                        + " WHERE FBWMANDANT=:MANDANT AND FBWBUCHDAT>'20140701' AND FBWBS IN ('01','10','11','12','13','20','21','22','23','26','27','30','31','32','33','34','35','36','37','38','40','41','42','43','46','47','50','51','52','53','55','56','57','58','73','76','77','78','80','88','89')"
                        + "  GROUP BY FBWMANDANT,FBWLO,FBWLB,FBWFC,FBWBUCHDAT,FBWAUFTYP,FBWBS,FBWAUFKRS,FBWAUFLFD,FBWAUFPOS,FBWAFOLGE,FBWART,FBWVAR,FBWFARBE,FBWGROE";
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    break;
                case 5:
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
        public void DeleteQVW_MCOST(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM QVW_MCOST X ";
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
        public DataSet QueryQVW_MCOST(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    //or1.CommandText = "SELECT * FROM QVW_MCOST WHERE  WDATE>'20140301'"; ;
                    or1.CommandText = "SELECT * FROM QVW_MCOST"; ;
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "QVW_MCOST";
            Return = Result;
            return Return;
        }
        public void UpdateQVW_MCOST(DataRow dwData, int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:

                    break;
            }
            Connect2Proxy(or1);
        }
    }
}
